package com.vertx.demo;

import com.vertx.demo.utils.BaseUtils;
import io.reactivex.Single;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.core.logging.Logger;
import io.vertx.core.logging.LoggerFactory;
import io.vertx.mysqlclient.MySQLConnectOptions;
import io.vertx.reactivex.core.AbstractVerticle;
import io.vertx.reactivex.mysqlclient.MySQLPool;
import io.vertx.reactivex.sqlclient.Row;
import io.vertx.reactivex.sqlclient.RowIterator;
import io.vertx.reactivex.sqlclient.RowSet;
import io.vertx.reactivex.sqlclient.Tuple;
import io.vertx.sqlclient.PoolOptions;

/**
 * Created by zhangls on 2020/7/9.
 */
public class MySQLRx2Verticle extends AbstractVerticle {

    private static final Logger LOGGER = LoggerFactory.getLogger(MySQLRx2Verticle.class);

    private MySQLPool client;

    private static final String selectSQL = "SELECT user_id,real_name,login_name,pass_word,phone_num,email,sts,create_time,update_time FROM tb_user WHERE sts=? AND user_id=?";

    private static final String crtDatabase = "CREATE DATABASE vertx_demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
    private static final String useDatabase = "use vertx_demo";

    private static final String crtTable = "CREATE TABLE IF NOT EXISTS tb_user (\n" +
        "   user_id INT UNSIGNED AUTO_INCREMENT,\n" +
        "   real_name VARCHAR(20) NOT NULL,\n" +
        "   login_name VARCHAR(20) NOT NULL,\n" +
        "   pass_word VARCHAR(20) NOT NULL,\n" +
        "   phone_num VARCHAR(20) NOT NULL,\n" +
        "   email VARCHAR(40) NOT NULL,\n" +
        "   sts CHAR(1) NOT NULL DEFAULT '1',\n" +
        "   create_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n" +
        "   update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n" +
        "   PRIMARY KEY (user_id)\n" +
        ")ENGINE=INNODB DEFAULT CHARSET=utf8";

    private static final String insSQL = "INSERT INTO tb_user(real_name,login_name,pass_word,phone_num,email) VALUES('张三','zhangsan','123456','13612345678','zhangsan@gmail.com')";

    private final MySQLConnectOptions connectOptions = new MySQLConnectOptions()
            .setCachePreparedStatements(true)
            .setPreparedStatementCacheMaxSize(100)
            .setConnectTimeout(5000)
            .setPort(3306)
            .setHost("127.0.0.1")
            .setDatabase("mysql")
            .setUser("root")
            .setPassword("123456")
            .setCharset("utf8mb4")
            .setCharacterEncoding("utf-8")
            .addProperty("useUnicode", "true")
            .addProperty("allowMultiQueries", "true")
            .addProperty("serverTimezone", "Hongkong");

    private final PoolOptions mySQLPoolOptions = new PoolOptions().setMaxSize(2);

    @Override
    public void start(Promise<Void> promise) throws Exception {
        client = MySQLPool.pool(vertx, connectOptions, mySQLPoolOptions);

        String sts = "1";
        String userId = "1";

        client.rxGetConnection().flatMap(conn -> {
            Single<RowSet<Row>> query = conn.query(crtDatabase).rxExecute()
                .flatMap(result -> conn.query(useDatabase).rxExecute())
                .flatMap(result -> conn.query(crtTable).rxExecute())
                .flatMap(result -> conn.query(insSQL).rxExecute())
                .flatMap(result -> conn.rxPrepare(selectSQL).flatMap(ps -> ps.query().rxExecute(Tuple.of(sts, userId))));
            return query.doAfterTerminate(conn::close);
        }).subscribe(rs -> {
            JsonArray res = new JsonArray();
            RowIterator<Row> rows = rs.value().iterator();
            while (rows.hasNext()) {
                JsonObject json = new JsonObject();
                Row row = rows.next();
                for (int i = 0; i < row.size(); i++) {
                    json.put(BaseUtils.underline2Camel(row.getColumnName(i), true), String.valueOf(row.getValue(i)));
                }
                res.add(json);
            }
            System.out.println(res.toString());
        }, Throwable::printStackTrace);

//        client.rxGetConnection().flatMap(conn -> {
//            Single<RowSet<Row>> rowSetSingle = conn.rxPrepare(SQL).flatMap(ps -> {
//                return ps.query().rxExecute(Tuple.of("1","1"));
//            });
//            return rowSetSingle.doAfterTerminate(conn::close);
//        }).subscribe(rs -> {
//            JsonArray res = new JsonArray();
//            RowIterator<Row> rows = rs.value().iterator();
//            while (rows.hasNext()) {
//                JsonObject json = new JsonObject();
//                Row row = rows.next();
//                for (int i = 0; i < row.size(); i++) {
//                    json.put(BaseUtils.underline2Camel(row.getColumnName(i), true), String.valueOf(row.getValue(i)));
//                }
//                res.add(json);
//            }
//            System.out.println(res.toString());
//        }, Throwable::printStackTrace);
    }

    @Override
    public void stop(Promise<Void> stopPromise) throws Exception {
        client.close();
    }
}
